<!--
  Licensed to the Apache Software Foundation (ASF) under one or more
  contributor license agreements.  See the NOTICE file distributed with
  this work for additional information regarding copyright ownership.
  The ASF licenses this file to You under the Apache License, Version 2.0
  (the "License"); you may not use this file except in compliance with
  the License.  You may obtain a copy of the License at

      http://www.apache.org/licenses/LICENSE-2.0

  Unless required by applicable law or agreed to in writing, software
  distributed under the License is distributed on an "AS IS" BASIS,
  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  See the License for the specific language governing permissions and
  limitations under the License.
-->
<BODY>
<h2> Support for Table Functions. </h2>

<P>
Derby lets you declare functions which return
<a href="http://java.sun.com/javase/6/docs/api/java/sql/ResultSet.html">ResultSet</a>s.
You can then use these function results as tables in your queries.
This, in turn, lets you do the following:
</P>

<UL>
<LI><B>Migrate</B> - Bulk-load data from an external
        database. The external data source could be any vendor's
        database.
</LI>
<LI><B>Integrate</B> - Transform live data from an existing
legacy server and load the data into Derby. This lets users
 build new Derby-powered apps against subsets of legacy data,
keep the data current, but limit the burden which the new apps
place on the legacy server.
</LI>
<LI><B>Snapshot</B> - Copy a subset of server data to a laptop before travelling.
</LI>
<LI><B>Federate</B> - Join data from multiple external data
sources. The external sources could be other relational databases
or they could be non-relational data feeds.
</LI>
</Ul>

<P>
Here is an example of how to declare and invoke a Table Function:
</P>

<BLOCKQUOTE><PRE style="color:blue; fontsize:125%;"><B>
CREATE FUNCTION externalEmployees
()
RETURNS TABLE
(
  employeeId    INT,
  lastName       VARCHAR( 50 ),
  firstName      VARCHAR( 50 ),
  birthday         DATE
)
LANGUAGE JAVA
PARAMETER STYLE DERBY_JDBC_RESULT_SET
NO SQL
EXTERNAL NAME 'com.acme.hrSchema.EmployeesTable.read'
;

INSERT INTO employees
  SELECT s.*
    FROM TABLE (externalEmployees() ) s;
</B></PRE></BLOCKQUOTE>

<P>
The Derby optimizer makes some assumptions about these Table Functions:
</P>

<UL>
<LI><B>Cost</B> - The optimizer hard-codes a guess about how expensive
  it is to materialize a Table Function.
</LI>
<LI><B>Count</B> - The optimizer also hard-codes a guess about how
many rows a Table Function returns.
</LI>
<LI><B>Repeatability</B> - The optimizer assumes that the same results
come back each time you invoke a Table Function.
</LI>
</Ul>

<P>
Based on these assumptions, the optimizer decides where to place the
Table Function in the join order. Using the interfaces in this package,
you may override the optimizer's guesses and force the optimizer to
choose a better join order.
</P>

<UL>
  <LI><a href="./VTICosting.html">VTICosting</a> - This interface
  exposes methods which let you override the optimizer's guesses.</LI>
  <LI><a href="./VTIEnvironment.html">VTIEnvironment</a> - This is a
  state variable, created by the optimizer and passed to the methods
  in <a href="./VTICosting.html">VTICosting</a>.
  <a href="./VTICosting.html">VTICosting</a> methods use this state
  variable to communicate with one another and learn more about the
  operating environment.</LI>
</UL>

</BODY>
